package cn.tit.ias.util.handlexcel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class HandleExcelUtil_ {

	/**
	 * 
	 * @Title: readExcel
	 * @Description: 读取excel文件
	 * @param: @param
	 *             file 转换后的io文件
	 * @param: @param
	 *             rowCount 读取的最大行数
	 * @param: @param
	 *             columnCount 读取的最大列数
	 * @return: Map<String,List<List<Object>>>
	 */
	public static Map<String, List<List<Object>>> readExcel(File file, Integer rowCount, Integer columnCount)
			throws FileNotFoundException, IOException, EncryptedDocumentException, InvalidFormatException {

		Map<String, List<List<Object>>> map = new HashMap<String, List<List<Object>>>();
		// 创建并获取文件流
		Workbook wb = WorkbookFactory.create(new FileInputStream(file));
		
//		Workbook wb = null;
//		try {
//		        wb = new XSSFWorkbook(new FileInputStream(file));
//		} catch (Exception ex) {
//		        wb = new HSSFWorkbook(new FileInputStream(file));
//		}

		// 开始读取数据
		// 解析sheet
		for (int i = 0; i < 1; i++) {
			Sheet sheet = wb.getSheetAt(i);
			List<List<Object>> dataList = new ArrayList<>();
			int readRowCount = 0;
			if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
				readRowCount = sheet.getPhysicalNumberOfRows();
			} else {
				readRowCount = rowCount;
			}
			// 解析sheet 的行
			for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
				Row row = sheet.getRow(j);
				// 盘点row是否为空
				if (isBlankRow(row)) {
					continue;
				}
				// 判断第一列是否为空
				if (row.getFirstCellNum() < 0) {
					continue;
				}
				// 定义读取列数
				int readColumnCount = 0;
				if (columnCount == null || columnCount > row.getLastCellNum()) {
					readColumnCount = (int) row.getLastCellNum();
				} else {
					readColumnCount = columnCount;
				}
				List<Object> rowValue = new LinkedList<Object>();
				// 解析sheet 的列
				for (int k = 0; k < readColumnCount; k++) {
					Cell cell = row.getCell(k);
					rowValue.add(getCellValue(wb, cell));
				}
				dataList.add(rowValue);
			}
			map.put("sheet", dataList);
		}
		return map;
	}

	/**
	 * 
	 * @Title: isBlankRow
	 * @Description: 判断row是否为空行
	 */
	public static boolean isBlankRow(Row row) {
		if (row == null)
			return true;
		boolean result = true;
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);
			String value = "";
			if (cell != null) {
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					value = String.valueOf((int) cell.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					value = String.valueOf(cell.getCellFormula());
					break;
				default:
					break;
				}

				if (!value.trim().equals("")) {
					result = false;
					break;
				}
			}
		}

		return result;
	}

	/**
	 * @Title: checkExcelFormat   
	 * @Description: 检查传文件格式是否符合模板要求   
	 */
	public static boolean checkExcelFormat(Map<String, List<List<Object>>> map) {
		List<List<Object>> sheet = map.get("sheet");
		List<Object> row = sheet.get(0);
		if(row.size() !=11)
			return false;
		if (row.get(0) == null)
			return false;
		if (row.get(1) == null )
			return false;
		if (row.get(2) == null)
			return false;
		if (row.get(3) == null)
			return false;
		if (row.get(4) == null)
			return false;
		if (row.get(5) == null)
			return false;
		if (row.get(6) == null)
			return false;
		if (row.get(7) == null)
			return false;
		if (row.get(8) == null)
			return false;
		if (row.get(9) == null)
			return false;
		if (row.get(10) == null)
			return false;
		return true;
	}

	/**
	 * 
	 * @Title: getFromTotalAssetExcel
	 * @Description: 获取 总资产中的重要字段
	 */
	public static Map<String, List<Map<String, Object>>> getFromTotalAssetExcel(Map<String, List<List<Object>>> map) {
		Map<String, List<Map<String, Object>>> mapresult = new HashMap<String, List<Map<String, Object>>>();
		// 迭代每一个sheet
		Iterator<String> keylist = map.keySet().iterator();
		while (keylist.hasNext()) {

			// 获取sheet的名称
			String key = (String) keylist.next();
			// 存放表中所有行row
			List<Map<String, Object>> rowList = new ArrayList<>();
			int rowsize = map.get(key).size();
			for (int i = 1; i < rowsize; i++) {
				Map<String, Object> columnlist = new HashMap<>();
				int columnsize = map.get(key).get(i).size();
				for (int j = 0; j < columnsize; j++) {
					// 每一列的键值
					String columnkey = null;
					Object temp;
					temp = map.get(key).get(i).get(j);
					if (j == 0)
						columnkey = "imUnitName";
					if (j == 2)
						columnkey = "imAssetRange";
					if (j == 4)
						columnkey = "imDocumentNum";
					if (j == 5)
						columnkey = "imAssetName";
					if (j == 8)
						columnkey = "imAssetModel";
					if (j == 11)
						columnkey = "imAssetPrice";
					if (j == 13)
						columnkey = "imFactory";
					if (j == 14)
						columnkey = "imBuyDate";
					if (j == 15)
						columnkey = "imTakePeople";
					if (j == 17)
						columnkey = "imRemark";
					if (j == 18)
						columnkey = "imProfitLoss";
					if (columnkey != null) {
						columnlist.put(columnkey, temp);
					}

				}
				rowList.add(columnlist);
			}
			mapresult.put("sheet", rowList);
		}
		return mapresult;
	}
	
	/**
	  * 获取资产表中的信息通过assetInfo.xlsx模板
	 * @param map
	 * @return
	 */
	public static Map<String, List<Map<String, Object>>> getFromTotalAssetExcel_1(Map<String, List<List<Object>>> map) {
		Map<String, List<Map<String, Object>>> mapresult = new HashMap<String, List<Map<String, Object>>>();
		// 迭代每一个sheet
		Iterator<String> keylist = map.keySet().iterator();
		while (keylist.hasNext()) {

			// 获取sheet的名称
			String key = (String) keylist.next();
			// 存放表中所有行row
			List<Map<String, Object>> rowList = new ArrayList<>();
			int rowsize = map.get(key).size();
			for (int i = 1; i < rowsize; i++) {
				Map<String, Object> columnlist = new HashMap<>();
				int columnsize = map.get(key).get(i).size();
				for (int j = 0; j < columnsize; j++) {
					// 每一列的键值
					String columnkey = null;
					Object temp;
					temp = map.get(key).get(i).get(j);
					if (j == 0)
						columnkey = "imUnitName";
					if (j == 1)
						columnkey = "imAssetRange";
					if (j == 2)
						columnkey = "imDocumentNum";
					if (j == 3)
						columnkey = "imAssetName";
					if (j == 4)
						columnkey = "imAssetModel";
					if (j == 5)
						columnkey = "imAssetPrice";
					if (j == 6)
						columnkey = "imFactory";
					if (j == 7)
						columnkey = "imBuyDate";
					if (j == 8)
						columnkey = "imTakePeople";
					if (j == 9)
						columnkey = "imRemark";
					if (j == 10)
						columnkey = "imProfitLoss";
					if (columnkey != null) {
						columnlist.put(columnkey, temp);
					}

				}
				rowList.add(columnlist);
			}
			mapresult.put("sheet", rowList);
		}
		return mapresult;
	}

	/**
	 * 
	 * @Title: getCellValue
	 * @Description: 获取cell中值
	 */
	private static Object getCellValue(Workbook wb, Cell cell) {
		Object val = "";
		try {
			// cell = row.getCell(column);
			NumberFormat nf = NumberFormat.getInstance();

			if (cell != null) {
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					// 处理日期问题
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						val = DateFormatUtils.format(date, "yyyy-MM-dd").replaceAll("", "");
					} else {
						val = nf.format(cell.getNumericCellValue());
						if (((String) val).indexOf(",") >= 0) {
							val = ((String) val).replace(",", "");
						}
					}

				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					val = cell.getStringCellValue().replaceAll("", "");
				} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					val = cell.getCellFormula().replaceAll("", "");
				} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					val = cell.getBooleanCellValue();
				} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
					val = cell.getErrorCellValue();
				}
			}
		} catch (Exception e) {
			return val;
		}
		return val;

	}


	/**
	 * 
	 * @Title: exportExcel @Description:导出excel表 @throws
	 */
	public static void exportExcel(List<List<Object>> assets, String[] head) {

		// 创建一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作表
		HSSFSheet sheet = workbook.createSheet("资产信息表");
		// 添加表头
		HSSFRow row = sheet.createRow(0);
		// 设置单元格格式居中
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 添加表头内容
		HSSFCell headCell;
		for (int i = 0; i < head.length; i++) {
			headCell = row.createCell(i);
			headCell.setCellValue(head[i]);
			headCell.setCellStyle(cellStyle);
			;
		}

		// 添加数据内容
		HSSFCell cell;
		for (int i = 0; i < assets.size(); i++) {
			List<Object> rowData = new ArrayList<>();
			row = sheet.createRow(i + 1);
			rowData = assets.get(i);
			for (int j = 0; j < rowData.size(); j++) {
				cell = row.createCell(j);
				cell.setCellValue((String) rowData.get(j));
				cell.setCellStyle(cellStyle);
			}
		}
		try {
			OutputStream outputStream = new FileOutputStream("E:\\excel.xls");
			workbook.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			System.out.println("导出失败");
			e.printStackTrace();
		}

	}
	

}
